package org.fao.pims.persistence;

import java.util.ArrayList;
import java.util.List;
import java.util.ListIterator;
import java.sql.*;
import java.util.*;
import org.apache.commons.logging.*;
import org.fao.pims.model.*;
import org.fao.pims.persistence.*;

public class DocumentDaoImpl extends BaseDao implements DocumentDao {

	private static Log log = LogFactory.getLog(PollinationNeedDaoImpl.class);

	public List getDocumentsOfPollNeed(PollinationNeed pollNeed) {
		
		PreparedStatement pstmt = null;
        ResultSet rset = null;
        Connection conn = null;
        List result = new ArrayList();
      
        try {
        	conn = this.getDatasource().getConnection();         

        	String query = 
        		"SELECT doc.*" +
        		"  FROM pollneed_document pxd," +
        		"       bib_document doc" +
        		" WHERE doc.id = pxd.document" +
        		"   AND doc.ref_man_db_name != 'freebook'" +
        		"   AND pxd.pollneed = " + pollNeed.getId();
        	
        	System.out.println(query);
        	
        	pstmt = conn.prepareStatement(query);
            rset = pstmt.executeQuery();    
            result = makeBeanList(resultsetToList(rset));

            // Set the documents contributors (agents)
            AgentDao agentDao = new AgentDaoImpl();
            ListIterator rowIterator = result.listIterator();
            while(rowIterator.hasNext())
            {
                Document doc = (Document) rowIterator.next();
                List agents = agentDao.getDocAgents(doc);
                doc.setContributorsList(agents);
                ListIterator agentIterator = agents.listIterator();
                String contribs = "";
                while(agentIterator.hasNext())
                {
                	Agent ag = (Agent) agentIterator.next();
                	contribs += ag.getName();
                	if (agentIterator.hasNext())
                		contribs += "; ";
                }
                doc.setContributors(contribs);
                // System.out.println(doc.getTitle());
                // System.out.println(doc.getContributors());
            }
        }
        catch(SQLException e) {
            e.printStackTrace();
        }
        finally {
            try 
            { 
                if( ! conn.isClosed() )
                    conn.close(); 
                System.out.println("Connection closed");
            }
            catch(Exception e) {
                e.printStackTrace();
            }
        }
		
		return result;
	}
	
	public List getDocumentsBySubject(String subject) {
		
		PreparedStatement pstmt = null;
        ResultSet rset = null;
        Connection conn = null;
        List result = new ArrayList();
      
        try {
        	conn = this.getDatasource().getConnection();         

        	String query = 
        		"SELECT doc.*" +
        		"  FROM bib_subject sbj, bib_document_subject dxs, bib_document doc" +
        		" WHERE doc.id = dxs.document" +
        		"   AND doc.ref_man_db_name != 'freebook'" +
        		"   AND sbj.id = dxs.subject" +
        		"   AND lower(sbj.term) like '%" + subject.toLowerCase() + "%'";
        	
        	System.out.println(query);
        	
        	pstmt = conn.prepareStatement(query);
            rset = pstmt.executeQuery();    
            result = makeBeanList(resultsetToList(rset));

            // Set the documents contributors (agents)
            AgentDao agentDao = new AgentDaoImpl();
            ListIterator rowIterator = result.listIterator();
            while(rowIterator.hasNext())
            {
                Document doc = (Document) rowIterator.next();
                List agents = agentDao.getDocAgents(doc);
                doc.setContributorsList(agents);
                ListIterator agentIterator = agents.listIterator();
                String contribs = "";
                while(agentIterator.hasNext())
                {
                	Agent ag = (Agent) agentIterator.next();
                	contribs += ag.getName();
                	if (agentIterator.hasNext())
                		contribs += "; ";
                }
                doc.setContributors(contribs);
                // System.out.println(doc.getTitle());
                // System.out.println(doc.getContributors());
            }
        }
        catch(SQLException e) {
            e.printStackTrace();
        }
        finally {
            try 
            { 
                if( ! conn.isClosed() )
                    conn.close(); 
                System.out.println("Connection closed");
            }
            catch(Exception e) {
                e.printStackTrace();
            }
        }
		
		return result;
	}
	
	public List getDocumentsByAuthor(String authorName) {
		
		PreparedStatement pstmt = null;
        ResultSet rset = null;
        Connection conn = null;
        List result = new ArrayList();
      
        try {
        	conn = this.getDatasource().getConnection();         

        	String query = 
        		"SELECT doc.*" +
        		"  FROM bib_agent agn, bib_document_agent dxa, bib_document doc" +
        		" WHERE doc.id = dxa.document_id" +
        		// "   AND doc.ref_man_db_name != 'freebook'" +
        		"   AND agn.id = dxa.agent_id" +
        		"   AND lower(agn.name) like '%" + authorName.toLowerCase() + "%'";
        	
        	System.out.println(query);
        	
        	pstmt = conn.prepareStatement(query);
            rset = pstmt.executeQuery();    
            result = makeBeanList(resultsetToList(rset));

            // Set the documents contributors (agents)
            AgentDao agentDao = new AgentDaoImpl();
            ListIterator rowIterator = result.listIterator();
            while(rowIterator.hasNext())
            {
                Document doc = (Document) rowIterator.next();
                List agents = agentDao.getDocAgents(doc);
                doc.setContributorsList(agents);
                ListIterator agentIterator = agents.listIterator();
                String contribs = "";
                while(agentIterator.hasNext())
                {
                	Agent ag = (Agent) agentIterator.next();
                	contribs += ag.getName();
                	if (agentIterator.hasNext())
                		contribs += "; ";
                }
                doc.setContributors(contribs);
                // System.out.println(doc.getTitle());
                // System.out.println(doc.getContributors());
            }
        }
        catch(SQLException e) {
            e.printStackTrace();
        }
        finally {
            try 
            { 
                if( ! conn.isClosed() )
                    conn.close(); 
                System.out.println("Connection closed");
            }
            catch(Exception e) {
                e.printStackTrace();
            }
        }
		
		return result;
	}
	
	@Override
	protected List makeBeanList(List rows) {
		List beanList = new ArrayList();
        
		System.out.println("rows size: "+ rows.size());
        ListIterator rowIterator = rows.listIterator();
        int rowcounter = 0;
        while(rowIterator.hasNext())
        {
            rowcounter++;
            List fieldlist = (List)rowIterator.next();
            ListIterator fieldsIterator = fieldlist.listIterator();
            Object obj = null;
            String type = "";
            String columnName = "";
            Document bean = new Document();
            
            while(fieldsIterator.hasNext())
            {
                TableFieldHandler tfhandler = (TableFieldHandler)fieldsIterator.next();
                obj = tfhandler.getObject();
                type = tfhandler.getType();
                columnName = tfhandler.getColumnName().trim();
                // System.out.println(columnName);
                if( obj != null ) {
	                if( "ID".equalsIgnoreCase(columnName) )
	                {
	                    bean.setId( ((Integer)obj).intValue() );                
	                }                	                
	                if( "TITLE".equalsIgnoreCase(columnName) )
	                {
	                    bean.setTitle( (String)obj );                    
	                }
	                if( "UNIQUE_ID".equalsIgnoreCase(columnName) )
	                {
	                    bean.setUniqueID( (String)obj );                    
	                }
	                if( "REF_MAN_DB_NAME".equalsIgnoreCase(columnName) )
	                {
	                    bean.setRefManDatabaseName( (String)obj );                    
	                }
	            }
            }
            beanList.add(bean);
         }
        return beanList;
	}
}
